﻿using System;
using System.Collections.Generic;
using System.Text;
using Shutterstock.Salesforce.Tools.SFPartner;
using System.Data;

namespace Shutterstock.Salesforce.SSISDownload
{
    public class SFTableInfo
    {
        public string TableName { get; private set; }
        public List<string> ColumnNames { get; private set; }
        public bool Replicateable { get; private set; }
        public bool Queryable { get; private set; }
        public bool Deletable { get; private set; }
        public bool Updateable { get; private set; }

        Dictionary<string, SFFieldInfo> m_Columns;


        public SFFieldInfo this[string columnName]
        {
            get
            {
                if (m_Columns.ContainsKey(columnName))
                {
                    return m_Columns[columnName];
                }
                else
                {
                    throw new KeyNotFoundException(String.Format("Could not find key:{0} in the colleciton",columnName));
                }
            }
        }
        /// <summary>
        /// Constructor
        /// </summary>
        public SFTableInfo()
        {
            TableName = string.Empty;
            ColumnNames = new List<string>();
            m_Columns = new Dictionary<string, SFFieldInfo>();
        }        

        public void Init(DescribeSObjectResult tableInfo)
        {
            foreach (Field field in tableInfo.fields)
            {
                m_Columns.Add(field.name, new SFFieldInfo(field));
                ColumnNames.Add(field.name);
            }
            ColumnNames.Sort();
            TableName = tableInfo.name;
            Replicateable = tableInfo.replicateable;
            Queryable = tableInfo.queryable;
            Deletable = tableInfo.deletable;
            Updateable = tableInfo.updateable;
        }

        public string GenerateCreateStagingTable()
        {
            
            if (ColumnNames.Count > 0)
            {
                string stagingTableName = GetStagingTableName();
                return GetCreateTable(stagingTableName);
            }

            return string.Empty;

        }


        public string GenerateCreateUpdateStagingTable()
        {

            if (ColumnNames.Count > 0)
            {
                string stagingTableName = GetStagingUpdateTableName();
                return GetCreateTable(stagingTableName);
            }
            return string.Empty;

        }

        public DataTable GenerateEmptyDataTable()
        {
            DataTable table = new DataTable();
            foreach (string columnName in ColumnNames)
            {
                SFFieldInfo fi = m_Columns[columnName];
                table.Columns.Add(columnName, SFTypeConverter.ConvertToType(fi.Type));
            }
            return table;

        }


        public string GenerateSelectAll()
        {
            if (ColumnNames.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select ");
                bool needsComma = false;

                ColumnNames.ForEach(column =>
                {
                    if (needsComma)
                        sb.Append(",");
                    else
                        needsComma = true;
                    sb.AppendLine(column);
                });
                sb.AppendLine(String.Format("From {0}", TableName));
                return sb.ToString();
            }
            return string.Empty;
        }

        public string GetStagingTableName()
        {
            return String.Format("staging_{0}", TableName);
        }

        public string GetStagingUpdateTableName()
        {
            return String.Format("stagingupdate_{0}", TableName);
        }

        public fieldType GetColumnType(string columnName)
        {
            if (!m_Columns.ContainsKey(columnName))
                return fieldType.anyType;
            else
                return m_Columns[columnName].Type;
        }

        public string GetTempTableName()
        {
            return String.Format("{0}_temp", TableName);
        }

        public string GetDeleteTableName()
        {
            return String.Format("{0}_delete", TableName);
        }

        public string GenerateCreateTempTableForDelete()
        {
            string tempName = GetDeleteTableName();
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(String.Format(" IF object_id('{0}') is not null", tempName));
            sb.AppendLine(" begin");
            sb.AppendLine(String.Format(" Drop Table {0}", tempName));
            sb.AppendLine(" end");
            sb.AppendLine(String.Format(" Create table {0} (", tempName));
            sb.AppendLine(" Id char(18) not null);");
            return sb.ToString();
        }

        public string CreateIDIndexForDeleteTable(string tempName)
        {
            return String.Format("Create index ind_1 on {0}(id);", tempName);
            
        }

        public string CreateIDIndexForUpdateTable()
        {
            string tempName = GetStagingUpdateTableName();
            return String.Format("Create index ind_1 on {0}(id);", tempName);

        }
          /// <summary>
        /// Whether the table contain a modifed date field
        /// </summary>
        /// <returns></returns>
        public bool HasModifiedDateField()
        {
            return ColumnNames.Contains("SystemModstamp") || ColumnNames.Contains("LastModifiedDate");
        }
        

        
        /// <summary>
        /// Get Create date
        /// </summary>
        /// <returns></returns>
        public string GetCreatedDateField()
        {
            if (ColumnNames.Contains("CreatedDate"))
                return "CreatedDate";
            else if (ColumnNames.Contains("SystemModstamp"))
                return "SystemModstamp";
            else if (ColumnNames.Contains("LoginTime"))
                return "LoginTime";

            else return "LastModifiedDate";
            
        }
        

        
        /// <summary>
        /// Get the modified Date field 
        /// Last Modified date only shows the user modified date
        /// SystemModstamp shows both user and system modified date
        /// </summary>
        /// <returns></returns>
        public string GetModifiedDateField()
        {
            if (ColumnNames.Contains("SystemModstamp"))
                return "SystemModstamp";
            else if (ColumnNames.Contains("LastModifiedDate"))
                return "LastModifiedDate";
            else
                return "CreatedDate";
        }
        

        
        /// <summary>
        /// Check whether the table contains the given column
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="maxLength"></param>
        /// <param name="numericScale"></param>
        /// <param name="numericPrecision"></param>
        /// <returns></returns>
        public bool ContainsColumn(string name, string type, int maxLength, int numericScale, int numericPrecision)
        {
            if (m_Columns.ContainsKey(name))
                return m_Columns[name].IsCompatibleType(type, maxLength, numericScale, numericPrecision);
            return false;
        }
        private string GetCreateTable(string stagingTableName)
        {
            StringBuilder sb = new StringBuilder();
            bool needsComma = false;
            sb.AppendLine(String.Format(" IF object_id('{0}') is not null", stagingTableName));
            sb.AppendLine(" begin");
            sb.AppendLine(String.Format(" Drop Table {0}", stagingTableName));
            sb.AppendLine(" end");
            sb.AppendLine(String.Format(" create table {0} (", stagingTableName));

            foreach (string columnName in ColumnNames)
            {
                if (needsComma)
                    sb.Append(",");
                else
                    needsComma = true;
                sb.AppendLine(m_Columns[columnName].GetSqlServerCreateTableString());
            }
            sb.AppendLine(")");
            string returnString = sb.ToString();
            return returnString;
        }


    }
}
